/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jforum.util;

import java.io.File;
import java.sql.*;
import jforum.domain.Ban;
import jforum.domain.Message;
import jforum.domain.User;
/**
 *
 * @author Вадим
 */
public class DBConnector {

    private static DBConnector instance;
    private Connection connection;
    private static boolean isPrintable = true;

    protected DBConnector() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");

        String database = "jforum";
        String username = "postgres";
        String password = "postgre";

        //String dbpath = new File(".").getAbsolutePath()+"/test.db";
        //System.out.println("Path to DB: "+dbpath);
        connection = DriverManager.getConnection("jdbc:postgresql:"+database, username , password);
        connection.setAutoCommit(true);
    }

    public static DBConnector getInstance() throws ClassNotFoundException, SQLException {
        if (instance==null)
            instance=new DBConnector();
        return instance;
    }

    public ResultSet executeQuery(String query) throws SQLException {
        Statement st = connection.createStatement();
        printSQL(query);
        return st.executeQuery(query);
    }

    public int executeUpdate(String query) throws SQLException {
        Statement st = connection.createStatement();
        printSQL(query);
        return st.executeUpdate(query);
    }

    public ResultSet executePreparedQuery(String query, int id) throws SQLException {
        PreparedStatement ps=connection.prepareStatement(query);
        ps.setInt(1, id);
        printSQL(query);
        return ps.executeQuery();
    }

    public ResultSet executePreparedQuery(String query, String x) throws SQLException {
        PreparedStatement ps=connection.prepareStatement(query);
        ps.setString(1, x);
        printSQL(query);
        return ps.executeQuery();
    }

    public ResultSet executePreparedQuery(String query, String x, String y) throws SQLException {
        PreparedStatement ps=connection.prepareStatement(query);
        ps.setString(1, x);
        ps.setString(2, y);
        printSQL(query);
        return ps.executeQuery();
    }

    public int executePreparedUpdate(String query, User user) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(query);

        ps.setString(1, user.getLogin());
        ps.setString(2, user.getPass());
        ps.setLong(3, user.reg_time.getTime());
        ps.setString(4, user.getEmail());
        ps.setString(5, user.act_key);
        ps.setShort(6, user.active);
        ps.setInt(7, user.rights);

        printSQL(query);
        return ps.executeUpdate();
    }

    public int executePreparedUpdate(String query, Message m) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(query);

        ps.setLong(1, m.getUser().getID());
        //ps.setLong(2, m.getParent_id());
        ps.setString(2, m.getText());
        ps.setLong(3, m.getCr_time().getTime());
        
        printSQL(query);
        return ps.executeUpdate();
    }

    public int executePreparedUpdate(String query, Message m, int pid) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(query);

        ps.setLong(1, m.getUser().getID());
        ps.setLong(2, pid);
        ps.setString(3, m.getText());
        ps.setLong(4, m.getCr_time().getTime());

        printSQL(query);
        return ps.executeUpdate();
    }

    public int executePreparedUpdate(String query, Ban b) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(query);
        ps.setLong(1, b.getUser().getID());
        ps.setLong(2, b.getStart_time().getTime());
        ps.setLong(3, b.getEnd_time().getTime());
        printSQL(query);
        return ps.executeUpdate();
    }

    public int executePreparedUpdate(String query, int id) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(query);
        ps.setInt(1, id);
        printSQL(query + " id="+id);
        return ps.executeUpdate();
    }


    
    private void printSQL(String query) {
        if (isPrintable)
            System.out.println("SQL: "+query);
    }

    public int initDB() throws SQLException {
        this.executeUpdate("drop table if exists users;");
        this.executeUpdate("drop table if exists reminds;");
        this.executeUpdate("VACUUM;");
        this.executeUpdate("create table users (user_id INTEGER PRIMARY KEY AUTOINCREMENT, login varchar(20),pass varchar(20));");
        this.executeUpdate("create table reminds (remind_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id int,name varchar(40), description text,importance int, type int , cr_time long);");
        //this.executeUpdate("create table downloads (download_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id int,sender varchar(20), description varchar(20),cr_time long);");
        this.executeUpdate("insert into users values (NULL,\"der\", \"password\");");
        this.executeUpdate("insert into users values (NULL,\"root\", \"lamo\");");
        this.executeUpdate("insert into users values (NULL,\"romgenn\", \"csf\");");
        return 1;
    }

}

